from typing import List

from sam import import_csv_path
from sam.app.base import cut_stats
from sam.util.dbUtil import mysql_env_config_dict, DB
from sam.util.fileUtil2 import read_file, write_multi_sheet_excel_quick


class BiliVideoAnalysis(object):

    def __init__(self):
        super().__init__()
        self.db = DB(mysql_env_config_dict.get("net"), level="debug")

    @staticmethod
    def fetch_all_keywords_from_file() -> List[str]:
        line_list = read_file(import_csv_path)
        return [line[0].strip() for line in line_list if line[0].strip()]

    def fetch_one_keyword_all_video_title(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="bili_list"
            , column_str="title"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [query_result[0] for query_result in query_result_list]

    def fetch_one_keyword_all_video_uuid(self, keyword) -> List[str]:
        query_result_list = self.db.query(
            table_name="bili_list"
            , column_str="uuid"
            , where=f" keyword = '{keyword}' "
            , optional="tuple"
        )
        return [query_result[0] for query_result in query_result_list]

    def fetch_one_keyword_all_video_dm(self, keyword) -> List[str]:
        uuid_list = self.fetch_one_keyword_all_video_uuid(keyword)
        if not uuid_list:
            return []
        uuid_str = "', '".join(uuid_list)
        query_result_list = self.db.query(
            table_name="bili_detail"
            , column_str="dm_text"
            , where=f" uuid in ('{uuid_str}') "
            , optional="tuple"
        )
        return [query_result[0] for query_result in query_result_list]

    def fetch_one_keyword_all_video_comment(self, keyword) -> List[str]:
        uuid_list = self.fetch_one_keyword_all_video_uuid(keyword)
        if not uuid_list:
            return []
        uuid_str = "', '".join(uuid_list)
        query_result_list = self.db.query(
            table_name="bili_detail"
            , column_str="comment_text"
            , where=f" uuid in ('{uuid_str}') "
            , optional="tuple"
        )
        return [query_result[0] for query_result in query_result_list]

    def video_title_analysis(self):
        keyword_list = self.fetch_all_keywords_from_file()
        sheet_name_content_map = {}
        for keyword in keyword_list:
            title_list = self.fetch_one_keyword_all_video_title(keyword)
            stats_result = cut_stats(line_list=title_list)
            sheet_name_content_map[keyword] = stats_result
        write_multi_sheet_excel_quick(
            sheet_name_content_map=sheet_name_content_map
            , export_file_name="神曲视频标题分词与统计"
        )

    def video_dm_analysis(self):
        keyword_list = self.fetch_all_keywords_from_file()
        sheet_name_content_map = {}
        whole_dm_list = []
        for keyword in keyword_list:
            dm_list = self.fetch_one_keyword_all_video_dm(keyword)
            whole_dm_list.extend(dm_list)
            stats_result = cut_stats(line_list=dm_list)
            sheet_name_content_map[keyword] = stats_result
        else:
            stats_result = cut_stats(line_list=whole_dm_list)
            sheet_name_content_map["10首歌整体分析"] = stats_result

        write_multi_sheet_excel_quick(
            sheet_name_content_map=sheet_name_content_map
            , export_file_name="10首歌B站视频弹幕分词与统计"
        )

    def video_comment_analysis(self):
        keyword_list = self.fetch_all_keywords_from_file()
        sheet_name_content_map = {}
        whole_comment_list = []
        for keyword in keyword_list:
            comment_list = self.fetch_one_keyword_all_video_comment(keyword)
            whole_comment_list.extend(comment_list)
            stats_result = cut_stats(line_list=comment_list)
            sheet_name_content_map[keyword] = stats_result
        else:
            stats_result = cut_stats(line_list=whole_comment_list)
            sheet_name_content_map["10首歌整体分析"] = stats_result

        write_multi_sheet_excel_quick(
            sheet_name_content_map=sheet_name_content_map
            , export_file_name="10首歌B站视频评论分词与统计"
        )

    def main(self):
        self.video_dm_analysis()
        self.video_comment_analysis()


def bili_video_title_analysis():
    process = BiliVideoAnalysis()
    process.main()


if __name__ == "__main__":
    bili_video_title_analysis()
